/** Call stored procedure from Java Code.
	1. Create the stored procedure in database.
	2. Create a CallableStatemet object by cnn.prepareCall("{call storedProName( ?, ? ....)}"
	3. Register each of all OUT parameters by
			cstmet.registerOutParameter(int pos, Type.XXX);
	4. Set IN parameters by cstmt.setXXX(int pos, value/variable);
	5. Execute the callable statement by cstmt.execute().

	Each update statement will be automatically committed if connection is set auto-commit to true.
When application exits, the transaction will be automatically committed by default if no cnn.rollback()
is called.
*/

import java.sql.*;
import java.io.*;

public class CallableStatement1 {

	Connection cnn;
	Statement  stmt;
	CallableStatement cstmt;
	ResultSet  res;

	String	sql = "SELECT * FROM EMP"; 

	String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
		 user="scott", passwd="tiger";

	public CallableStatement1() {
	   try {
             	try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
		catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }
		cnn = DriverManager.getConnection(url, user, passwd);

		// Dont save updates until commit called.
		cnn.setAutoCommit(false);

		stmt = cnn.createStatement();
		// create a callable statement from connection object
		cstmt = cnn.prepareCall("{call increaseSalary( ?, ? )}");
		// if parameter is a OUT parameter, it must be registered.

		// Register output parameter.
		cstmt.registerOutParameter(2, Types.INTEGER);

	   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
	}

	public static void main(String argv[]) {
		CallableStatement1 ex2 = new CallableStatement1(); // get connected, and create stmt.
		byte ans[] = new byte[120];
		char c = ' ';
		String str ;
		int inc, tot;

		while ( true ) {

			ex2.executeSQL(ex2.sql);
			// ex2.displayResultSet();
			ScreenIO.displayResultSet(ex2.res);
			for ( int i = 0; i < ans.length; i ++ ) ans[i] = (byte) ' ';
			str = ScreenIO.promptForString("Enter amount to increment salary or Q/e to quite ... ");
			if ( str == null || str.equals("") ) continue;
			try { c = str.charAt(0); } catch (StringIndexOutOfBoundsException e) {}
			if ( c == 'q' || c == 'Q' ) ex2.exit();
			try {
				inc= Integer.parseInt(str);
				ex2.callIncreaseSalary(inc);

				// get value from OUT parameter.
				System.out.println("\n\n" + ex2.cstmt.getInt(2) + " rows updated in folowing list:");

			} catch (NumberFormatException e) { System.out.println("Illegal data format"); continue; }
			  catch (SQLException eq) {System.out.println("Illegal Cal to Stored Procedure"); }
		}
	}

	void callIncreaseSalary( int inc ) {
		int  tot = 0;
	    try {
		cstmt.setInt(1, inc);
		cstmt.execute();
	    } catch (SQLException eq) {System.out.println("Illegal Cal to Stored Procedure"); }
	}

	void executeSQL(String sql) {
	   try {
		System.out.println("\n");
		res = stmt.executeQuery(sql);
	   } catch (SQLException e) { e.printStackTrace(); }
	}
	protected void exit() {
	   try {
		char c = ScreenIO.promptForChar("Save any change made (Y/N) : ");
		// Without call to rollback, the default will commit the changes.
		if ( c != 'Y' &&  c != 'y' ) cnn.rollback();
		else cnn.commit();
		stmt.close();
		res.close();
		cnn.close();
		System.exit(0);
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) {}
	}
}
